from wt.connector.db_manager.db_untils_tools import DBUntilsTools
from wt.connector.data_connect import DataConnect

# doris_v1
class DBDorisManger(DataConnect, DBUntilsTools):

    def __init__(self, version=1):
        super().__init__()
        self.version = version

    def create_ddl(self, table_name):
        """
        定义doris_v1插入字段类型
        :return:
        """
        sql_template = """CREATE TABLE {table_name} (
            `id` int(11) NOT NULL,
            `smallint_col` int(11) NULL,
            `integer_col` double NULL,
            `bigint_col` float NULL,
            `decimal_col` smallint(6) NULL
        )
        UNIQUE KEY (`id`)
        DISTRIBUTED BY HASH(`id`) BUCKETS 10
        PROPERTIES (
        "replication_allocation" = "tag.location.default: 1",
        "in_memory" = "false",
        "storage_format" = "V2",
        "disable_auto_compaction" = "false"
        );
        """

        return sql_template.format(table_name=table_name)

    def generate_create_ddl(self, db, table):
        return self.create_ddl(table_name=table)

    def generate_create_ddl(self, db, table):
        """
        拼接doris_v1 ddl语句
        :param table:
        :return:
        """
        ddl_sql = self.create_ddl(table)
        return ddl_sql

    def general_col_value(self, table):
        """
        生成 doris_v1 dml
        :param table:
        :return:
        """
        id_val = self.get_random_id()
        c_02_integer_val = self.get_random(-2147483648, 2147483648)
        c_03_double_val = self.get_random_float(-500, 500)
        c_04_float_val = self.get_random_float(-500, 500)
        c_05_smallint_val = self.get_random(-32768, 32767)
        c_06_bigint_val = self.get_random(-9223372036854775808, 9223372036854775807)
        c_07_tinyint_val = self.get_random(-128, 123)
        c_09_varchar_val = self.get_random_string(8)
        c_10_date_val = self.get_random_date()
        c_11_datetime_val = self.get_random_datetime()
        sql = "insert into {} (id, c_02_integer_col, c_03_double_col, c_04_float_col, c_05_smallint_col, c_06_bigint_col," \
              "c_07_tinyint_col, c_09_varchar_col, c_10_date_col, c_11_datetime_col) values ({}, {}, {}, {}, {}, {}, {}, {}, {},{})".format(
            table, id_val, c_02_integer_val, c_03_double_val, c_04_float_val, c_05_smallint_val, c_06_bigint_val, c_07_tinyint_val, c_09_varchar_val, c_10_date_val, c_11_datetime_val)
        return sql

    def generate_dml_sql(self, table,lines):
        """
         批量生成 doris_v1 dml
        :param table:
        :return:
        """
        insert_sql = ""
        while lines > 0:
            tmp = self.general_col_value(table)
            insert_sql = insert_sql + tmp + "\r\n"
            lines = lines - 1
        print(insert_sql)
        return insert_sql

    def execute_create_and_insert_sql(self, db, table, num, is_recreate=None):
        """
        生成ddl、插入dml
        :param db:
        :param table:
        :param num:
        :param is_recreate:
        :return:
        """
        if is_recreate:
            self.exec_doris_sql_by_version(db, "drop table if exists {}".format(table), )
            ddl_sql = self.generate_create_ddl(db, table)
            self.exec_doris_sql_by_version(db, ddl_sql,)
        insert_sql = self.generate_dml_sql(table, num)
        print(insert_sql)
        for line in insert_sql.strip().split("\r\n"):
            self.exec_doris_sql_by_version(db, line,)

    def show_table_sql(self, db, table):
        """
        展示doris表结构
        :param db: 数据库名
        :param table: 表名
        :return:
        """
        query_sql = f"SHOW CREATE TABLE {table}"
        print(query_sql)
        res = self.exec_doris_sql_by_version(db, query_sql)
        print(res)

    def get_db_data(self, db, table):
        sql = " {}.{}".format(db, table)
        res = self.exec_doris_sql_by_version(db, sql)
        return self.set_return_massage(res, only_data="yes")

    def create_and_insert_sql(self, db, table, num, is_recreate=None):
        """
        生成ddl、插入dml
        :param is_recreate:
        :param db:
        :param table:
        :param num:
        :return:
        """
        if is_recreate:
            ddl_sql = self.create_ddl(table)
            self.exec_doris_sql_by_version(db, "drop table if exists {}".format(table))
            self.exec_doris_sql_by_version(db, ddl_sql)
        insert_sql = self.generate_dml_sql(table, num)
        print(insert_sql)
        for line in insert_sql.strip().split("\r\n"):
            self.exec_doris_sql_by_version(db, line)

    def get_count(self, db, table):
        """
        获取doris表数据量
        :param db: 数据库名
        :param table: 表名
        :return: 数据量
        """
        query_sql = f"select count(*) from {table}"
        print(query_sql)
        res = self.exec_doris_sql_by_version(db, query_sql)
        print(res)
        if res['message'] == "Success":
            res_num = res['data'][0][0]
            return res_num
        else:
            return None

    def exec_doris_sql_by_version(self, db='', sql_str='',):
        if self.version == 1:
            return self.exec_doris_sql(db, sql_str)
        else:
            return self.exec_doris_sql(db, sql_str, version=2)

    def exec_customer_sql(self, db, sql):
        res = self.exec_doris_sql(db, sql)
        return res

    def get_db_data(self, db, table):
        sql = "select * from {}.{} order by id desc limit 10".format(db, table)
        res = self.exec_doris_sql_by_version(db, sql)
        return self.set_return_massage(res, only_data="yes")

    def execute_drop_table(self, db, table):
        """
        删除表
        :param db: 数据库名
        :param table: 表名
        :return: None
        """
        res = self.exec_doris_sql_by_version(db, "drop table if exists {}".format(table))
        return self.set_return_massage(res)

if __name__ == '__main__':
    db = "qa_source"
    table = "auto_test_long_type"
    num = 10
    # doris = DBDorisManger()
    # doris.create_and_insert_sql(db, table, num, True,)
    doris = DBDorisManger()
    doris.create_and_insert_sql(db, table, num, True)
    # doris.show_table_sql(db, table)
    # doris.execute_drop_table()
    # print(doris.get_count(db, table))
    # doris.set_primary_combkey(db, table)
    # doris.get_count(db, table)
    # doris.execute_create_and_insert_sql(db, table, num)
    # doris.get_count(db, table)
